﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using Contributor.Data_Objects;

namespace Contributor
{
    class FindPeople
    {

        public static List<EntitySearch> LoadSearchData(string whereClause)
        {

            OleDbConnection conn = new OleDbConnection(AppConfig.ConnectionStr);

            conn.Open();
            OleDbCommand cmd = conn.CreateCommand();
            cmd.CommandType = System.Data.CommandType.Text;

            cmd.CommandText = @" 
SELECT p.FirstName, p.LastName, e.SalutationHousehold, e.AddressSalutation, e.CompanyName, e.EntityID
FROM dbo_Person p
INNER JOIN dbo_Entity e ON p.EntityId = e.EntityID
" + whereClause;

            OleDbDataReader rdr = cmd.ExecuteReader();

            List<EntitySearch> list = new List<EntitySearch>();
            while (rdr.Read())
            {
                EntitySearch person = new EntitySearch();
                try
                {
                    person.FirstName = DbLib.GetString(rdr,0);
                    person.LastName = DbLib.GetString(rdr, 1);
                    person.SalutationHousehold = DbLib.GetString(rdr, 2);
                    person.AddressSalutation = DbLib.GetString(rdr, 3);
                    person.CompanyName = DbLib.GetString(rdr, 4);
                    person.EntityID = rdr.GetInt32(5);
                }
                catch (Exception exc)
                {
                    person.FirstName = exc.Message;
                }
                list.Add(person);
            }

            return list;
        }

        public static List<ImportData> LoadData(string whereClause)
        {

            OleDbConnection conn = new OleDbConnection(AppConfig.ConnectionStr);

            conn.Open();
            OleDbCommand cmd = conn.CreateCommand();
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.CommandText = @"SELECT 
ID,
GiftOfficer,
OldID,
DateJoined,
ENF,
SalutationHousehold,
AddressSalutation,
FirstNameHead,
LastNameHead,
FirstNamePartner,
LastNamePartner,
DOB,
Title,
CompanyName,
Category,
Department,
StreetAddress,
StreetAddress2,
City,
State,
Country,
PostalCode,
WorkPhone,
WorkPhoneExt,
HomePhone,
CellPhone,
Fax,
EmailAddress,
NoNewsletter
            FROM ImportData " + whereClause;
            OleDbDataReader rdr = cmd.ExecuteReader();

            List<ImportData> list = new List<ImportData>();
            while (rdr.Read())
            {
                ImportData person = new ImportData();
                try
                {
                    person.ID = rdr.GetInt32(0);
                    person.GiftOfficer = DbLib.GetString(rdr,1);
                    person.OldID = DbLib.GetInt16(rdr,2);
                    person.DateJoined = DbLib.GetDateTime(rdr,3);
                    person.ENF = DbLib.GetString(rdr,4);
                    person.SalutationHousehold = DbLib.GetString(rdr,5);
                    person.AddressSalutation = DbLib.GetString(rdr,6);
                    person.FirstNameHead = DbLib.GetString(rdr,7);
                    person.LastNameHead = DbLib.GetString(rdr,8);
                    person.FirstNamePartner = DbLib.GetString(rdr,9);
                    person.LastNamePartner = DbLib.GetString(rdr,10);
                    person.DOB = DbLib.GetString(rdr,11);
                    person.Title = DbLib.GetString(rdr,12);
                    person.CompanyName = DbLib.GetString(rdr,13);
                    person.Category = DbLib.GetString(rdr,14);
                    person.Department = DbLib.GetString(rdr,15);
                    person.StreetAddress = DbLib.GetString(rdr,16);
                    person.StreetAddress2 = DbLib.GetString(rdr,17);
                    person.City = DbLib.GetString(rdr,18);
                    person.State = DbLib.GetString(rdr,19);
                    person.Country = DbLib.GetString(rdr,20);
                    person.PostalCode = DbLib.GetString(rdr,21);
                    person.WorkPhone = DbLib.GetString(rdr,22);
                    person.WorkPhoneExt = DbLib.GetString(rdr,23);
                    person.HomePhone = DbLib.GetString(rdr,24);
                    person.CellPhone = DbLib.GetString(rdr,25);
                    person.Fax = DbLib.GetString(rdr,26);
                    person.EmailAddress = DbLib.GetString(rdr,27);
                    person.NoNewsletter = DbLib.GetString(rdr,28);
                }
                catch (Exception exc)
                {
                    person.FirstNameHead = exc.Message;
                }
                list.Add(person);
            }

            return list;
            //return DbSupportLib.Support.LoadList(new List<Person>(), rdr);
        }
    }
}
